﻿/*========================== 
 * @author 郑金泉
 * @desc 数据库表类
 * ========================= */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

using MySql.Data;
using MySql.Data.MySqlClient;
using Obsidian.Data.Sql.SqlSrvClient;

namespace Obsidian.Data.Sql
{
    public class DbTable
    {

        private Dictionary<string, DbField> dictFields = new Dictionary<string, DbField>(StringComparer.OrdinalIgnoreCase);


        private DataBase _dataBase = null;
        public DataBase DataBase
        {
            get { return this._dataBase; }
        }

        private string _tableName = null;
        public string Name
        {
            get { return this._tableName; }
        }

        private DbField _primaryKey = null;
        public DbField PimaryKey
        {
            get { return this._primaryKey; }
        }

        private DbField[] _fields = null;
        public DbField[] Fields
        {
            get { return this._fields; }
        }

        public DbTable(DataBase db, string tableName)
        {
            if (db == null)
                throw new Exception("参数\"db\"不能为null");
            if(String.IsNullOrEmpty(tableName))
                throw new Exception("表名称不能为空!");
            this._dataBase = db;
            this._tableName = tableName;
            this.GetTableSchema(db, tableName);
        }

        /// <summary>
        /// 根据字段名获得字段
        /// </summary>
        /// <param name="fieldName">字段名</param>
        /// <returns></returns>
        public DbField GetFieldByName(string fieldName)
        {
            if (dictFields.ContainsKey(fieldName))
                return dictFields[fieldName];
            else
                return null;
        }

        /// <summary>
        /// 获得表结构
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="tableName">表名称</param>
        private void GetTableSchema(DataBase db, string tableName)
        {
            switch (db.Type)
            {
                case DataBaseType.SqlServer:
                    this.GetTableSchemaSqlSrv(db, tableName);
                    break;
                case DataBaseType.OleDb:
                    this.GetTableSchemaOleDb(db, tableName);
                    break;
                case DataBaseType.MySql:
                    this.GetTableSchemaMySql(db, tableName);
                    break;
                default:
                    throw new Exception("未知数据库类型");
            }
        }

        #region SqlServer

        /// <summary>
        /// 获得SqlServer数据库的表结构
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="tableName">表名称</param>
        private void GetTableSchemaSqlSrv(DataBase db, string tableName)
        {
            SqlSrvExecutor executor = new SqlSrvExecutor(db.ConnectionString);
            StringBuilder sbCmd = new StringBuilder();
            sbCmd.Append("sp_MShelpcolumns '");
            if (!String.IsNullOrEmpty(db.Owner))
                sbCmd.Append("[").Append(db.Owner).Append("].");
            sbCmd.Append("[").Append(tableName).Append("]");
            sbCmd.Append("'");
            string cmdText = sbCmd.ToString();
            IDataReader dataReader = executor.ExecuteReader(CommandType.Text, cmdText);
            List<DbField> fieldList = new List<DbField>();
            bool first = true;
            while (dataReader.Read())
            {
                int fc = dataReader.FieldCount;
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(tableName);
                for (int j = 0; j < fc; j++)
                {
                    sb.AppendLine(dataReader.GetName(j));
                }

                bool isPrimary = false;
                if (first)
                {
                    isPrimary = true;
                    first = false;
                }

                string colName = Convert.ToString(dataReader["col_name"]);
                string typeName = Convert.ToString(dataReader["col_typename"]).ToLower();
                bool allowNull = Convert.ToBoolean(dataReader["col_null"]);
                //bool isPrimary = Convert.ToBoolean(dataReader["col_identity"]);
                bool isAutoinc = dataReader["col_increment"].ToString() == "1";
                DataType dataType = ToDataTypeFromSqlSrv(typeName);
                if (dataType == DataType.Unknown)
                {
                    throw new Exception("未知数据类型:\"" + typeName + "\" " + 
                        "(\"" + tableName + " - " + colName + "\")");
                }
                DbField field = new DbField(this, colName, isPrimary, allowNull, isAutoinc, dataType);
                if (isPrimary)
                    this._primaryKey = field;
                fieldList.Add(field);
                dictFields.Add(colName, field);
            }
            dataReader.Close();
            this._fields = fieldList.ToArray();
        }

        private static DataType ToDataTypeFromSqlSrv(string typeName)
        {
            switch (typeName)
            {
                case "bit":
                    return DataType.Bit;
                case "int":
                case "tinyint":
                case "smallint":
                    return DataType.Int;
                case "bigint":
                    return DataType.Bigint;
                case "money":
                case "decimal":
                case "numeric":
                    return DataType.Decimal;
                case "float":
                    return DataType.Float;
                case "char":
                    return DataType.Char;
                case "varchar":
                case "nvarchar":
                    return DataType.VarChar;
                case "text":
                case "ntext":
                    return DataType.Text;
                case "date":
                case "datetime":
                    return DataType.DateTime;
                default:
                    return DataType.Unknown;
            }
        }

        #endregion

        #region OleDb

            
        /// <summary>
        /// 获得Access数据库的表结构
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="tableName">表名称</param>
        private void GetTableSchemaOleDb(DataBase db, string tableName)
        {
            DataTable dtColumns = GetOleDbColumnInfo(db, tableName);
            DataTable dtPrimary = GetOleDbPrimaryInfo(db, tableName);
            List<DbField> fieldList = new List<DbField>();
            foreach (DataRow row in dtColumns.Rows)
            {
                string colName = Convert.ToString(row["COLUMN_NAME"]);
                string typeName = Convert.ToString(row["DATA_TYPE"]).ToLower();
                bool allowNull = Convert.ToBoolean(row["IS_NULLABLE"]);
                bool isPrimary = dtPrimary.Select(string.Format("COLUMN_NAME='{0}'", row["COLUMN_NAME"].ToString())).Length > 0 ? true : false;
                bool isAutoinc = isPrimary;
                DataType dataType = ToDataTypeFromOleDb(typeName);
                if (dataType == DataType.Unknown)
                {
                    throw new Exception("未知数据类型:\"" + typeName + "\" " +
                        "(\"" + tableName + " - " + colName + "\")");
                }
                DbField field = new DbField(this, colName, isPrimary, allowNull, isAutoinc, dataType);
                if (isPrimary)
                    this._primaryKey = field;
                fieldList.Add(field);
                dictFields.Add(colName, field);
            }
            this._fields = fieldList.ToArray();
        }
        /// <summary>  
        /// 获取Access表列信息  
        /// </summary>  
        /// <returns></returns>  
        public static DataTable GetOleDbColumnInfo(DataBase db, string tableName)
        {
            using (OleDbConnection connection = new OleDbConnection(db.ConnectionString))
            {
                connection.Open();
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null });
                DataView view = new DataView();
                view.Table = dt;
                view.RowFilter = string.Format("table_name='{0}'", tableName);
                return view.ToTable();
            }
        }  

        /// <summary>  
        /// 获取Access表主键信息  
        /// </summary>  
        /// <param name="tableName"></param>  
        /// <returns></returns>  
        public static DataTable GetOleDbPrimaryInfo(DataBase db, string tableName)
        {
            using (OleDbConnection connection = new OleDbConnection(db.ConnectionString))
            {
                connection.Open();
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null });
                DataView view = new DataView();
                view.Table = dt;
                view.RowFilter = string.Format("table_name='{0}'", tableName);
                return view.ToTable();
            }
        }

        private static DataType ToDataTypeFromOleDb(string typeName)
        {
            switch (typeName)
            {
                case "17":
                case "bit":
                    return DataType.Bit;
                case "2":
                case "int":
                    return DataType.Int;
                case "bigint":
                case "3":
                    return DataType.Bigint;
                case "money":
                case "decimal":
                    return DataType.Decimal;
                case "float":
                    return DataType.Float;
                case "char":
                    return DataType.Char;
                case "varchar":
                case "nvarchar":
                case "130":
                    return DataType.VarChar;
                case "text":
                case "ntext":
                    return DataType.Text;
                case "7":
                case "date":
                case "datetime":
                    return DataType.DateTime;
                case "11":
                    return DataType.Bool;
                default:
                    return DataType.Unknown;
            }
        }
        #endregion

        #region MySql
        
        /// <summary>
        /// 获得MySql数据库的表结构
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="tableName">表名称</param>
        private void GetTableSchemaMySql(DataBase db, string tableName)
        {
            using (MySqlConnection connection = new MySqlConnection(db.ConnectionString))
            {
                connection.Open();
                try
                {
                    DataTable table = connection.GetSchema("Columns", new string[] { null, null, tableName, null });

                    if (table.Rows.Count == 0)
                        throw new Exception("找不到映射表 - " + tableName);
                    List<DbField> fieldList = new List<DbField>();
                    foreach (DataRow r in table.Rows)
                    {
                        string colName = r[3].ToString();
                        object defaultValue = r[5];
                        bool allowNull = r[6].ToString() == "YES";
                        string typeName = r[7].ToString();
                        int size = Convert.IsDBNull(r[8]) ? -1 : Convert.ToInt32(r[8]);

                        bool isPrimary = r[14].ToString() == "PRI";
                        if (!isPrimary)
                            isPrimary = r[15].ToString() == "PRI";

                        //bool isUNI = isPrimary ? isPrimary : (r[14].ToString() == "UNI");
                        bool isAutoinc = r[15].ToString().Equals("auto_increment", StringComparison.OrdinalIgnoreCase) ||
                            r[16].ToString().Equals("auto_increment", StringComparison.OrdinalIgnoreCase);
                        DataType dataType = ToDataTypeFromSqlSrv(typeName);
                        DbField field = new DbField(this, colName, isPrimary, allowNull, isAutoinc, dataType);

                        if (isPrimary)
                            this._primaryKey = field;
                        fieldList.Add(field);
                        dictFields.Add(colName, field);
                    }
                    this._fields = fieldList.ToArray();
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        private static DataType ToDataTypeFromMySql(string type)
        {
            switch (type.ToLower())
            {
                case "bit":
                    return DataType.Bit;
                case "int":
                    return DataType.Int;
                case "bigint":
                    return DataType.Bigint;
                case "varchar":
                    return DataType.VarChar;
                case "text":
                    return DataType.Text;
                case "date":
                    return DataType.DateTime;
                case "datetime":
                    return DataType.DateTime;
                case "decimal":
                    return DataType.Decimal;
                default:
                    throw new Exception("找不到对应类型 - " + type);
            }
        }


        #endregion

    }
}
